package com.wing.cms.dao;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.net.MalformedURLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.Column;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.wing.cms.domain.Artgroup;
import com.wing.cms.domain.Article;
import com.wing.cms.domain.Article_group;
import com.wing.cms.domain.Channel;
import com.wing.cms.domain.Layout;
import com.wing.cms.domain.Lookfeel;
import com.wing.cms.domain.Placeholder;
import com.wing.cms.domain.Portlet;
import com.wing.cms.domain.Portlet1;
import com.wing.cms.domain.Portlet2;
import com.wing.cms.domain.Portlet3;
import com.wing.cms.domain.Portlet4;
import com.wing.cms.domain.Portlet_C;
import com.wing.cms.domain.Site;
import com.wing.cms.domain.Template;
import com.wing.cms.ext.domain.CmsFunction;
import com.wing.common.CommonSpringJDBC;
import com.wing.common.Constants;
import com.wing.common.util.ApplicationPath;
import com.wing.common.util.DateUtils;
import com.wing.common.util.JavaScriptUtils;
import com.wing.common.util.PageBean;
import com.wing.ext.domain.Productgroup;
import com.wing.ext.domain.Products;
import com.wing.sys.domain.Auth;



@Repository
public class SiteDao extends CommonSpringJDBC<Site, String> {

	public PageBean findPageList(int currentPage, int pageSize, String seachv) {
		String where= " 1 = 1 ";
		if(seachv!=null && !"".equals(seachv)){
			where += "  and (site_name like '%"+seachv+"%' or site_url like '%"+seachv+"%') ";
		}
		String sql1="SELECT * " +
				" FROM cms_site where "+where+" ORDER BY order_num ASC,create_time desc " +
				" LIMIT "+(currentPage-1) * pageSize+","+pageSize;
		String sql2 = "select count(id) from cms_site  where"+where;
		String[] sqlArray = {sql1,sql2};
		return super.findPageList(sqlArray, currentPage, pageSize);
	}
	
	public List getSitesBySiteMgrs(String userId) {
		String where= " 1 = 1 ";
		String sql="SELECT cs.* " +
			" FROM cms_site cs,sys_auth sr where cs.id=sr.res_id and sr.owner_id='"+userId+"' ORDER BY cs.order_num ASC,cs.id ASC ";
		if(Constants.SYSTEM_SUPERADMIN_ID.equals(userId)){
			sql="SELECT * " +
				" FROM cms_site ORDER BY order_num ASC,id ASC ";
		}
		return super.getSimpleJdbcTemplate().queryForList(sql);
	}
	
	public List getSitesBySiteUser(String userId) {
		String sql="SELECT cs.* " +
			" FROM cms_site cs,sys_user u where cs.id <> '"+Constants.WORK_SITE+"' and cs.id=u.site_id and u.id='"+userId+"' ORDER BY cs.order_num ASC,cs.id ASC ";
		if(Constants.SYSTEM_SUPERADMIN_ID.equals(userId)){
			sql="SELECT * " +
				" FROM cms_site ORDER BY order_num ASC,id ASC ";
		}
		return super.getSimpleJdbcTemplate().queryForList(sql);
	}

	public List<Map<String, Object>> getSitesBySitePath(String siteId,String site_path) {
		String sql="SELECT cs.* " +" FROM cms_site cs where cs.site_path='"+site_path+"' ";
		if(siteId!=null){
			sql += " and cs.id != '"+siteId+"'";
		}
		return super.getSimpleJdbcTemplate().queryForList(sql);
	}
	
	public void cloneSite(String[] sql) {
		this.getSimpleJdbcTemplate().getJdbcOperations().batchUpdate(sql);
	}
	 
	//创建复制文章表的sql
	public String[] creatCloneArticleSQL(String siteId,String newSiteId,String destSitePath,String rodmomCPId) {
		String sql = "select t.* from cms_article t where t.site_id=? ";
		Object[] objs = { siteId };
		List<Map<String, Object>> groups = super.getSimpleJdbcTemplate()
				.queryForList(sql, objs);
		String[] sqls = new String[groups.size()+1];
		for (int i = 0; i < groups.size(); i++) {
			String  article_path = (String)groups.get(i).get("article_path");
			String[] tts = article_path.split("/");
			String newPath = "site";
			for(int oo = 0; oo < tts.length; oo++){
				if(oo == 0){continue;}
				else if(oo == 1){newPath +="/"+destSitePath;}
				else if(oo == 2){newPath +="/document";}
				else{newPath += "/"+rodmomCPId+tts[oo];}
			}
			String insertSQL = "insert into cms_article values ('"+rodmomCPId
						+ groups.get(i).get("id") + "','"
						+ newSiteId + "','"
						+  "','"
						+  "','"
						+  "','"
						+ groups.get(i).get("author") + "','"
						+ newPath + "','"
						+ groups.get(i).get("create_user") + "','"
						+ groups.get(i).get("create_time") + "','"
						+ groups.get(i).get("valid_flag") + "','"
						+ groups.get(i).get("ordernum") + "','"
						+ groups.get(i).get("status") + "')";
			sqls[i] = insertSQL;
		}		
		sqls[groups.size()] =  "UPDATE cms_article a ,cms_article b  set  a.art_name =  b.art_name , " +
		"a.summary = b.summary,a.keyword = b.keyword WHERE  a.id =  concat('"+rodmomCPId+"',b.id)";
		return sqls;
	}

	//创建备份整个站点的sql
	public void creatBakSiteSQL(String siteId){
		try {
		StringBuffer sqlb = new StringBuffer();
		Site site = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_site where id = ?",
				ParameterizedBeanPropertyRowMapper.newInstance(Site.class), siteId);
		/////////////
		sqlb.append(builderInsertSqlByEntity(site,"cms_site")+";").append("\r\n");
		////////////////////////
		List<Map<String, Object>> l_chanel = getSimpleJdbcTemplate().queryForList("select id from cms_channel where site_id = '"+siteId+"'");
		for(int i = 0; i < l_chanel.size(); i ++){
			Channel c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_channel where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Channel.class), l_chanel.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_channel")+";").append("\r\n");;
		}
		///////////////////////
		List<Map<String, Object>> l_cms_artgroup = getSimpleJdbcTemplate().queryForList("select id from cms_artgroup where site_id = '"+siteId+"'");
		for(int i = 0; i < l_cms_artgroup.size(); i ++){
			Artgroup c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_artgroup where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Artgroup.class), l_cms_artgroup.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_artgroup")+";").append("\r\n");;
		}
		/////////////////////////////
		List<Map<String, Object>> l_cms_article = getSimpleJdbcTemplate().queryForList("select id from cms_article where site_id = '"+siteId+"'");
		for(int i = 0; i < l_cms_article.size(); i ++){
			Article c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_article where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Article.class), l_cms_article.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_article")+";").append("\r\n");;
		}
		/////////////////////////////
		List<Map<String, Object>> l_cms_article_group = getSimpleJdbcTemplate().queryForList("select id from  cms_article_group where artgroup_id in(select id from cms_artgroup where site_id = '"+siteId+"')");
		for(int i = 0; i < l_cms_article_group.size(); i ++){
			Article_group c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_article_group where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Article_group.class), l_cms_article_group.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_article_group")+";").append("\r\n");;
		}
		///////////////////////////////
		List<Map<String, Object>> l_cms_placeholder = getSimpleJdbcTemplate().queryForList("select id from  cms_placeholder where channelid  in (select id from cms_channel where site_id = '"+siteId+"')");
		for(int i = 0; i < l_cms_placeholder.size(); i ++){
			Placeholder c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_placeholder where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Placeholder.class), l_cms_placeholder.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_placeholder")+";").append("\r\n");;
		}
		//文章布局的占位符
		List<Map<String, Object>> l_cms_placeholder_art = getSimpleJdbcTemplate().queryForList("select id from  cms_placeholder where channelid like '%,"+siteId+"%'");
		for(int i = 0; i < l_cms_placeholder_art.size(); i ++){
			Placeholder c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_placeholder where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Placeholder.class), l_cms_placeholder_art.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_placeholder")+";").append("\r\n");;
		}
		///////////////////////////////
		List<Map<String, Object>> l_cms_portletinstance = getSimpleJdbcTemplate().queryForList("select id from  cms_portletinstance where id in(select portletId from cms_placeholder where channelid in ( select id from cms_channel where site_id = '"+siteId+"'))");
		for(int i = 0; i < l_cms_portletinstance.size(); i ++){
			Portlet c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_portletinstance where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Portlet.class), l_cms_portletinstance.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_portletinstance")+";").append("\r\n");;
		}
		//文章布局的
		List<Map<String, Object>> l_cms_portletinstance_art = getSimpleJdbcTemplate().queryForList("select id from  cms_portletinstance where id in(select portletId from cms_placeholder where channelid like '%,"+siteId+"%')");
		for(int i = 0; i < l_cms_portletinstance_art.size(); i ++){
			Portlet c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_portletinstance where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Portlet.class), l_cms_portletinstance_art.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_portletinstance")+";").append("\r\n");;
		}
		///////////////////////////////
		List<Map<String, Object>> l_cms_portletinstance1 = getSimpleJdbcTemplate().queryForList("select id from  cms_portletinstance1 where id in(select portletId from cms_placeholder where channelid in ( select id from cms_channel where site_id = '"+siteId+"'))");
		for(int i = 0; i < l_cms_portletinstance1.size(); i ++){
			Portlet1 c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_portletinstance1 where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Portlet1.class), l_cms_portletinstance1.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_portletinstance1")+";").append("\r\n");;
		}
		//文章布局的
		List<Map<String, Object>> l_cms_portletinstance1_art = getSimpleJdbcTemplate().queryForList("select id from  cms_portletinstance1 where id in(select portletId from cms_placeholder where channelid  like '%,"+siteId+"%')");
		for(int i = 0; i < l_cms_portletinstance1_art.size(); i ++){
			Portlet1 c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_portletinstance1 where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Portlet1.class), l_cms_portletinstance1_art.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_portletinstance1")+";").append("\r\n");;
		}
		///////////////////////////////
		List<Map<String, Object>> l_cms_portletinstance2 = getSimpleJdbcTemplate().queryForList("select id from  cms_portletinstance2 where id in(select portletId from cms_placeholder where channelid in ( select id from cms_channel where site_id = '"+siteId+"'))");
		for(int i = 0; i < l_cms_portletinstance2.size(); i ++){
			Portlet2 c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_portletinstance2 where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Portlet2.class), l_cms_portletinstance2.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_portletinstance2")+";").append("\r\n");;
		}
		//文章布局的
		List<Map<String, Object>> l_cms_portletinstance2_art = getSimpleJdbcTemplate().queryForList("select id from  cms_portletinstance2 where id in(select portletId from cms_placeholder where channelid like '%,"+siteId+"%')");
		for(int i = 0; i < l_cms_portletinstance2_art.size(); i ++){
			Portlet2 c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_portletinstance2 where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Portlet2.class), l_cms_portletinstance2_art.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_portletinstance2")+";").append("\r\n");;
		}
//		///////////////////////////////
		List<Map<String, Object>> l_cms_portletinstance3 = getSimpleJdbcTemplate().queryForList("select id from  cms_portletinstance3 where id in(select portletId from cms_placeholder where channelid in ( select id from cms_channel where site_id = '"+siteId+"'))");
		for(int i = 0; i < l_cms_portletinstance3.size(); i ++){
			Portlet3 c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_portletinstance3 where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Portlet3.class), l_cms_portletinstance3.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_portletinstance3")+";").append("\r\n");;
		}
		//文章布局的
		List<Map<String, Object>> l_cms_portletinstance3_art = getSimpleJdbcTemplate().queryForList("select id from  cms_portletinstance3 where id in(select portletId from cms_placeholder where channelid like '%,"+siteId+"%')");
		for(int i = 0; i < l_cms_portletinstance3_art.size(); i ++){
			Portlet3 c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_portletinstance3 where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Portlet3.class), l_cms_portletinstance3_art.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_portletinstance3")+";").append("\r\n");;
		}
		///////////////////////////////
		List<Map<String, Object>> l_cms_portletinstance4 = getSimpleJdbcTemplate().queryForList("select id from  cms_portletinstance4 where id in(select portletId from cms_placeholder where channelid in ( select id from cms_channel where site_id = '"+siteId+"'))");
		for(int i = 0; i < l_cms_portletinstance4.size(); i ++){
			Portlet4 c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_portletinstance4 where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Portlet4.class), l_cms_portletinstance4.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_portletinstance4")+";").append("\r\n");;
		}
		//文章布局的
		List<Map<String, Object>> l_cms_portletinstance4_art = getSimpleJdbcTemplate().queryForList("select id from  cms_portletinstance4 where id in(select portletId from cms_placeholder where channelid like '%,"+siteId+"%')");
		for(int i = 0; i < l_cms_portletinstance4_art.size(); i ++){
			Portlet4 c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_portletinstance4 where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Portlet4.class), l_cms_portletinstance4_art.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_portletinstance4")+";").append("\r\n");;
		}
		
		///////////////////////////////
		List<Map<String, Object>> l_cms_portletinstance_c = getSimpleJdbcTemplate().queryForList("select id from  cms_portletinstance_c where id in(select portletId from cms_placeholder where channelid in ( select id from cms_channel where site_id = '"+siteId+"'))");
		for(int i = 0; i < l_cms_portletinstance_c.size(); i ++){
			Portlet_C c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_portletinstance_c where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Portlet_C.class), l_cms_portletinstance_c.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_portletinstance_c")+";").append("\r\n");;
		}
		//文章布局的
		List<Map<String, Object>> l_cms_portletinstance_c_art = getSimpleJdbcTemplate().queryForList("select id from  cms_portletinstance_c where id in(select portletId from cms_placeholder where channelid like '%,"+siteId+"%')");
		for(int i = 0; i < l_cms_portletinstance_c_art.size(); i ++){
			Portlet_C c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_portletinstance_c where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Portlet_C.class), l_cms_portletinstance_c_art.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_portletinstance_c")+";").append("\r\n");;
		}
		
		///////////////////////////////
		List<Map<String, Object>> l_ext_productgroup = getSimpleJdbcTemplate().queryForList("select id from ext_productgroup where site_id = '"+siteId+"'");
		for(int i = 0; i < l_ext_productgroup.size(); i ++){
			Productgroup c = this.getSimpleJdbcTemplate().queryForObject("select * from  ext_productgroup where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Productgroup.class), l_ext_productgroup.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"ext_productgroup")+";").append("\r\n");;
		}
		///////////////////////////////
		List<Map<String, Object>> l_ext_products = getSimpleJdbcTemplate().queryForList("select id from ext_products where site_id = '"+siteId+"'");
		for(int i = 0; i < l_ext_products.size(); i ++){
			Products c = this.getSimpleJdbcTemplate().queryForObject("select * from  ext_products where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Products.class), l_ext_products.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"ext_products")+";").append("\r\n");;
		}
		///////////////////////////////
		List<Map<String, Object>> l_cms_layout = getSimpleJdbcTemplate().queryForList("select id  from  cms_layout where ispub ='0' and id in (select res_id from sys_auth where owner_id ='"+siteId+"')");
		for(int i = 0; i < l_cms_layout.size(); i ++){
			Layout c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_layout where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Layout.class), l_cms_layout.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_layout")+";").append("\r\n");;
		}
		///////////////////////////////
		List<Map<String, Object>> l_cms_template = getSimpleJdbcTemplate().queryForList("select id  from  cms_template where ispub ='0' and id in (select res_id from sys_auth where owner_id ='"+siteId+"')");
		for(int i = 0; i < l_cms_template.size(); i ++){
			Template c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_template where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Template.class), l_cms_template.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_template")+";").append("\r\n");;
		}
		///////////////////////////////
		List<Map<String, Object>> l_cms_function = getSimpleJdbcTemplate().queryForList("select id  from  cms_function where ispub ='0' and id in (select res_id from sys_auth where owner_id ='"+siteId+"')");
		for(int i = 0; i < l_cms_function.size(); i ++){
			CmsFunction c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_function where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(CmsFunction.class), l_cms_function.get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_function")+";").append("\r\n");;
		}
		///////////////////////////////
		List<Map<String, Object>> l_cms_lookfeel  = getSimpleJdbcTemplate().queryForList("select id  from  cms_lookfeel  where ispub ='0' and id in (select res_id from sys_auth where owner_id ='"+siteId+"')");
		for(int i = 0; i < l_cms_lookfeel .size(); i ++){
			Lookfeel c = this.getSimpleJdbcTemplate().queryForObject("select * from  cms_lookfeel  where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Lookfeel.class), l_cms_lookfeel .get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"cms_lookfeel ")+";").append("\r\n");;
		}
		///////////////////////////////
		List<Map<String, Object>> l_sys_auth  = getSimpleJdbcTemplate().queryForList("select id  from  sys_auth where res_id = '"+siteId+"' and type ='site'");
		for(int i = 0; i < l_sys_auth .size(); i ++){
			Auth c = this.getSimpleJdbcTemplate().queryForObject("select * from  sys_auth  where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Auth.class), l_sys_auth .get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"sys_auth ")+";").append("\r\n");;
		}
		///////////////////////////////
		List<Map<String, Object>> l_sys_auth1  = getSimpleJdbcTemplate().queryForList("select id  from  sys_auth where owner_id = '"+siteId+"'");
		for(int i = 0; i < l_sys_auth1 .size(); i ++){
			Auth c = this.getSimpleJdbcTemplate().queryForObject("select * from  sys_auth  where id = ?",
					ParameterizedBeanPropertyRowMapper.newInstance(Auth.class), l_sys_auth1 .get(i).get("id"));
			sqlb.append(builderInsertSqlByEntity(c,"sys_auth ")+";").append("\r\n");;
		}
		///////////////////////////////
		
		   
		   
				File file = new File(ApplicationPath.getParameter("sitebak")+site.getSite_path()+DateUtils.dateToString(new Date())+".sql");
				if(file.exists()){
					file.delete();
				}
				BufferedWriter bw=null;
		      try {
		         file.createNewFile();
		    	 bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file),"utf-8")); 
		         bw.write(sqlb.toString());
		     	 bw.flush();
		      } catch (MalformedURLException mue) {
		         System.out.println("出错了! 此URL格式不正确.");
		         mue.printStackTrace();
		        
		      } catch (IOException ioe) {
		         System.out.println("出错了! 发生IO异常.");
		         ioe.printStackTrace();
		      } finally {
		         try {
		        	if(bw!=null){
		        		bw.close();
		        	}
		         } catch (IOException ioe) {
		        	 ioe.printStackTrace();
		         }
		      }
		      
//		      		StringBuffer str = new StringBuffer("");   
//		              String st = "";   
//		            try {   
//		                 FileInputStream fs = new FileInputStream("D:\\test.txt");   
//		                 InputStreamReader isr;   		                  
//		                 isr = new InputStreamReader(fs);                      
//		                 BufferedReader br = new BufferedReader(isr);   
//		                  try {   
//		                     String data = "";   
//		                      while ((data = br.readLine()) != null) {   
//		                        str.append(data);   
//		                     }   
//		                  } catch (Exception e) {   
//		                      str.append(e.toString());   
//		                 }   
//		                  st = str.toString();   
//		                 if (st != null && st.length() > 1)   
//		                     st = st.substring(0, st.length() - 1);   
//		              } catch (IOException es) {   
//		                 st = "";   
//		              }  
//		              String[] sql = st.split(";");
//		              this.getSimpleJdbcTemplate().getJdbcOperations().batchUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	// 创建新增sql
	private String builderInsertSqlByEntity(Object entity,String tabName) throws IllegalArgumentException, IllegalAccessException{
		StringBuffer sb = new StringBuffer();
		Map<String, String> colAndField = new HashMap<String, String>();
		Map<String, String> colAndFieldV = new HashMap<String, String>();
		Map<String, String> colAndFieldT = new HashMap<String, String>();
		Field[] fields = entity.getClass().getDeclaredFields();
		for (Field f : fields) {
			f.setAccessible(true); 
			Annotation[] fileNnnotations = f.getAnnotations();
			for (Annotation an : fileNnnotations) {
				if (an.annotationType().getSimpleName().equals("Column")) {
					colAndField.put(((Column) an).name(), f.getName());
					if(f.get(entity) == null){
						colAndFieldV.put(((Column) an).name(),"");
					}else{
						colAndFieldV.put(((Column) an).name(), JavaScriptUtils.javaScriptEscape(f.get(entity).toString()));
					}
					colAndFieldT.put(((Column) an).name(), f.getGenericType().toString());
				}
			}

		}
		StringBuffer colStrings = new StringBuffer();
		StringBuffer fieldStrings = new StringBuffer();
		colStrings.append(" (");
		fieldStrings.append(" (");
		for (String dataKey : colAndField.keySet()) {
			if(colAndFieldT.get(dataKey).equals("int")){
				colStrings.append(dataKey).append(",");
				fieldStrings.append("").append((colAndFieldV.get(dataKey))).append(
						",");
			}else{
				colStrings.append(dataKey).append(",");
				fieldStrings.append("'").append((colAndFieldV.get(dataKey))).append(
						"',");
			}
			
		}
		sb.append("REPLACE into ").append(tabName).append(
				colStrings.toString().substring(0,
						colStrings.toString().length() - 1));
		sb.append(")");
		sb.append(" values ");
		sb.append(fieldStrings.toString().substring(0,
				fieldStrings.toString().length() - 1));
		sb.append(")");
		return sb.toString();
	}

	public PageBean getCpSites(int currentPage, int pageSize,String site) {
		String where= " remarks like '复制自%' and " +
				" site_url like '%"+site+".com' ";
		String sql1="SELECT * FROM cms_site where " + where+" order by create_time desc "+
		" LIMIT "+(currentPage-1) * pageSize+","+pageSize;
		String sql2 = "select count(id) from cms_site  where"+where;
		String[] sqlArray = {sql1,sql2};
		return super.findPageList(sqlArray, currentPage, pageSize);
	}

	public List getSitesBySiteUrl(String url) {
		String sql="SELECT cs.* " +" FROM cms_site cs where cs.site_url='"+url+"' ";
		return super.getSimpleJdbcTemplate().queryForList(sql);
	}
}
